using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.IO;

namespace SqlCeDriver
{
	// was a driver class: now a helper class
	public abstract class SqlCeDataSource
	{
		//////////////////////////////////////////////////////////
		// Central resource.
		// 
		public abstract SqlCEDataAccessor GetAccessor(); // { return new SqlCEDataAccessor35(); }

		// Who is this driver?
		// need overwriting
		protected virtual String DriverNameInternal { get { return "Sql Server Compact Edition"; } }
		protected virtual Version VersionInternal {	get { return Version.UNKNOWN; } }

		// exposed access for helper (processor classes)
		internal bool IsCaseSensitive {get { return this.isCaseSensitive; } }
		internal int SqlVersion {get { return this.MapVersion(this.sqlVersion); } }

		public SqlCeDataSource()
		{
			// initialise classes to handle various groups of driver functions
			// DataTable generation from SQL
			this.tableCommandProcessor = new TableCommandProcessor(this);
			//// Source code generation and searching
			this.sourcecodeCommandProcessor = new SourcecodeCommandProcessor(this);
			//// other functions 
			this.extensionCommandProcessor = new ExtensionCommandProcessor(this);
		}
		
		public String GetDriverName() { return this.DriverNameInternal; }

		public String GetConnectionString() { return this.connectionString; }
		// alternative: perhaps reduce to one one day
		public String ConnectionString { get { return this.connectionString; } set {this.connectionString = value;} } // set added for a new interface implementation
		
		public void SetConnectionString(String connStr) { this.connectionString = connStr; }
		
		// _Impl methods were interface members now base class interface implementation members
		protected bool PerformDriverCheck_Impl(out String configErrorMessage)
		{
			try
			{
				// 
				this.GetAccessor().AccessDatabaseEngineType();
				configErrorMessage = "OK";
				return true;
			}
			catch (FileNotFoundException fnfExc)
			{
				// SQL CE is not installed
				configErrorMessage = String.Format(
@"The SQL CE Driver could not access its database connection class. This normally 
means that SQL CE is not installed on this machine. SQL CE must be installed from
the Microsoft web site before DbViewSharp can work with it. Search for ""Microsoft
SQL CE download"". Note the additional instructions for 64 bit machines. If you 
are sure that SQL CE is installed on your machine please not the error below
and refer to the DbViewSharp website for further information.

{0}", fnfExc.Message);
				return false;
			}
			catch (Exception exc)
			{
				// not expecting this ever (ha ha)
				configErrorMessage = String.Format(
@"The SQL CE Driver has hit an unexpected error condition. Details are below. 
Please refer to the DbViewSharp website for details on how to fix this. If
there are none then please consider contacting the project administrator 
with details. The message reads:

{0}", exc.Message);
				return false;
			}
		}
		
		protected void SetCurrentConnection_Impl(DbView.ConnectionInfo connectionInfo)
		{
			this.connectionString = BuildConnectionString(connectionInfo);
		}

		public bool SupportsCommand_Impl(DbView.Commands.Cmnd command)
		{
			String commandTemplate = SqlCeTemplates.Templates.FetchTemplate(this.MapVersion(this.sqlVersion), command);
			return (commandTemplate.Length > 0);
		}

		//////////////////// CommandBuilder Interface ////////
		public String MakeCommand(DbView.Commands.Cmnd command, DbView.ViewContext context)
		{
			// get the version as it may change the SQL used
			if (this.sqlVersion == Version.UNKNOWN) this.DetermineSqlProperties();
			// the idea for plugins is to register builders here
			// for now we'll just deal with known 
			String filterString = (this.isCaseSensitive) ? context.FilterString.ToUpper() : context.FilterString;
			// look up
			String commandTemplate = SqlCeTemplates.Templates.FetchTemplate(this.MapVersion(this.sqlVersion), command);
			// if no additional parameter assume just the filter is required
			if (this.tableCommandProcessor.SimpleTemplate(commandTemplate))
			{
				return this.tableCommandProcessor.AddFilterClause(commandTemplate, filterString);
			}
			switch (command)
			{
				case DbView.Commands.Cmnd.Adhoc:
					return filterString;
				case DbView.Commands.Cmnd.Fields:
					return this.tableCommandProcessor.MakeFieldsCommandString(context, commandTemplate, filterString);
				case DbView.Commands.Cmnd.TableData:
				{
					String tableName = this.TableNameFromContext(context);
					return this.tableCommandProcessor.TableDataCommandString(tableName, context.ViewDef, filterString);
				}
				case DbView.Commands.Cmnd.Aggregate:
					return this.tableCommandProcessor.AggregateDataCommandString(commandTemplate, context);
				case DbView.Commands.Cmnd.RowCount:
					return this.tableCommandProcessor.RowCountCommandString(commandTemplate, context);
				case DbView.Commands.Cmnd.MinMax:
					return this.tableCommandProcessor.RowCountCommandString(commandTemplate, context);
				case DbView.Commands.Cmnd.Average:
					return this.tableCommandProcessor.RowCountCommandString(commandTemplate, context);
				case DbView.Commands.Cmnd.LongShort:
					return this.tableCommandProcessor.RowCountCommandString(commandTemplate, context);
				case DbView.Commands.Cmnd.TableFieldSearch:
					return this.sourcecodeCommandProcessor.SearchSourceCommand(context);
				/*
				// boilerplate simple stuff from here.
				case DbView.Commands.cmd.JobSteps:	
				{
					String jobId = JobIdFromContext(context);
					return String.Format(commandTemplate, filterString, jobId);
				}
				*/
			}
			return ""; // ie unsupported
		}
		
		// The version may decide the sql template to use.
		void DetermineSqlProperties()
		{
		}

		private string SearchSourceCommand(DbView.ViewContext context, String template)
		{
			String searchText = context.Command["SEARCH"]; // what to search ffor
			String filter = context.FilterString;          // what to filter object names on (rarely used?)
			// replace search string using the 
			//template = SqlCeTemplates.Templates.ProcessFilterClause(template, "{", searchText, this.m_CaseSensitive);
			template = SqlCeTemplates.Templates.ProcessFilterClause(template, filter, this.isCaseSensitive);
			String commandString = String.Format(template, searchText);
			return commandString;
		}

		public String MakeExtensionCommand(String commandTag, DbView.ViewContext context)
		{
			// the idea for plugins is to register builders here
			// for now we'll just deal with known 
			return ""; // ie unsupported
		}
		
		///////////////////////////////////////////////////////////////
		// IScripting
		//// public DbViewDriver.IScripting Scripter { get { return this; } }

		public String ScriptData(DbViewDriver.IDataSource source, String tableName)
		{
			throw new NotImplementedException();
			/*
			SqlTableSchema schema = MakeSchemaFor(tableName);
			SqlServerScripter Scripter = new SqlServerScripter(tableName, schema);
			DataSet DSet = new DataSet();
			Helper.GenericMakeDset(this.m_Connstr, "select * from "+tableName, tableName, out DSet);
			DataTable dataTable = DSet.Tables[0];
			*/
			//return Scripter.ScriptData(dataTable);
		}

		///////////////////////////////////////////////////////////////
		// ISqlOperations
		//
		public String ScriptSynchContents(DbViewDriver.IDataSource source, String tableName, DbView.ConnectionInfo refreshSourceConnInfo)
		{
			throw new NotImplementedException();
		}

		public void CopyTable(DbViewDriver.IDataSource source, String sourceTableName, DbView.ConnectionInfo copyTargetConnInfo, String targetTableName)
		{
			throw new NotImplementedException();
		}
		// Static helper for other classes in the driver
		public static String BuildConnectionString(DbView.ConnectionInfo connectionInfo)
		{
			String fileName = Path.Combine(connectionInfo.Server, connectionInfo.Database);
			String sizeParam = GetMaxDbSize(connectionInfo);
			String connStr = (connectionInfo.PWD.Length > 0) ? String.Format("DataSource={0};Password='{1}';{2}", fileName, connectionInfo.PWD, sizeParam) :
																String.Format("DataSource={0};Persist Security Info=False;{1}", fileName, sizeParam);
			return connStr;
		}
		
		// having discovered the size limit on SQL CE databases we need to include the override parameter Max Database Size=nnn
		// however for now we'll hijack the username parameter. In the future we may introduce a proper "other connections options" it would be better.
		// each property will be stored as NAME=VALUE
		// No. YAGNI. just solve the problem
		public static String GetMaxDbSize(DbView.ConnectionInfo connectionInfo)
		{
			if (connectionInfo.UID.Length == 0) return ""; // normal
			String tokens = connectionInfo.UID+";"; // add a terminator just in case. Doesn't matter for now
			String seek = "MAXDB";
			int pos1 = tokens.IndexOf(seek);
			if (pos1 == -1) return "";
			pos1 += seek.Length + 1; // skip =
			int pos2 = tokens.IndexOf(";", pos1);
			if (pos2 > pos1)
			{
				int sizeMb;
				if (int.TryParse(tokens.Substring(pos1, pos2-pos1), out sizeMb))
					return String.Format("Max Database Size={0};", sizeMb);
			}
			return "";
		}

		public static bool IsValid(DbView.ConnectionInfo connectionInfo)
		{
			// all commections need a name
			if (connectionInfo.Name.Length == 0) return false;
			// Hm test please.
			if (connectionInfo.Server.Length == 0 || connectionInfo.Database.Length == 0)
				return false;
			// It may be OK without user and pwd as it will switch to 
			// trusted connection
			return true;
		}			

		//////////////////// helpers ////////
		int MapVersion(Version sqlVersion)
		{
			return (sqlVersion == Version.SqlCE35) ? 3 : -1;
		}

		public void PopulateTable(String command, String tableName, out DataTable table, out bool isReadOnly)
		{
			table = new Helper(this.GetAccessor()).GenericQuery(this.connectionString, command, tableName);
			isReadOnly = false; // work this out later.
		}

		// core Interface method to build a data table
		public DataTable BuildTable_Impl(DbView.Command command, DbView.ViewContext context)
		{
			// 
			String commandString = this.MakeCommand(command.Cmd, context);
			if (commandString.Length == 0)
				throw new Exception(String.Format("Driver Error: {0} generated an empty command string", command));
			
			return this.tableCommandProcessor.BuildDataTable(command, commandString, context);
		}

		public String GetSchemaSql(String tableName)
		{
			// get the correct template
			String sqlTemplate = SqlCeTemplates.Templates.FetchTemplate(this.MapVersion(this.sqlVersion), DbView.Commands.Cmnd.Fields);
			// remove special where clause as the result should always include all fields.
			sqlTemplate = SqlCeTemplates.Templates.ProcessFilterClause(sqlTemplate, "", this.isCaseSensitive);
			// return template with required tablename added
			return String.Format(sqlTemplate, tableName,"");
		}

		void AddColumnMetaData(DbView.DataTableExtraInfoManager mgr)
		{
			String fieldnameColumn = "ColumnName";
			// need a query
			String sqlTemplate = SqlCeTemplates.Templates.FetchTemplate(this.MapVersion(this.sqlVersion), DbView.Commands.Cmnd.Fields);
			String commandString = String.Format(sqlTemplate, mgr.Table.TableName, "");
			DataSet dset = new DataSet();
			DataTable table = new Helper(this.GetAccessor()).GenericQuery(this.connectionString, commandString, mgr.Table.TableName);
			
			// check for pkeys
			int pkeyCount = 0;
			foreach (DataRow row in table.Rows)
				if (row["PKey?"].ToString().Length > 2) ++pkeyCount;
			bool modifiable = (pkeyCount > 0); // can't modify a table w/o a pkey
			// mark up pkeys, indexes and fkeys for colouring
			foreach (DataColumn col in mgr.Table.Columns)
			{
				String search = String.Format("[{1}]='{0}'", col.ColumnName, fieldnameColumn);
				DataRow [] row = table.Select(search);
				if (row.GetLength(0) > 0)
				{
					if (row[0]["PKey?"].ToString().Length > 2)
					{
						mgr.SetPkey(col);
						--pkeyCount; // counting down to 0
					}
					if (row[0]["FKey?"].ToString().Length > 2)
						mgr.SetFkey(col);
					if (row[0]["Index?"].ToString().Length > 2)
						mgr.SetIndexed(col);
				}
				if (modifiable && pkeyCount == 0) // not all pkeys are in the view
					mgr.SetModifiable();
			}
		}

		protected bool Update_Impl(DataTable table, out String statusMessage)
		{
			// need to build some sql
			DbView.ViewDefinition viewDef = null;
			// DbView.ViewDefinition viewDef = ViewDefinitionManager.makeNewViewDefinition(Table);
			String sql = this.tableCommandProcessor.TableDataCommandString(table.TableName, viewDef, "");

			// cur from old code
			int modCount = 0;
			foreach (DataRow dr in table.Rows)
				if (dr.RowState != DataRowState.Unchanged)
					++modCount;
			if (this.GetAccessor().Update(this.connectionString, sql, table, out statusMessage) < 0)
				return false;
			statusMessage = String.Format("{0} changed rows", modCount);
			return true;
		}

		protected String GetSourceCode_Impl(DbView.Command command)
		{
			return this.sourcecodeCommandProcessor.ProcessCommand(command);
		}
		
		//public SqlTableSchema MakeSchemaFor(String tableName)
		//{
		//    String commandTemplate = SqlCeTemplates.Templates.FetchTemplate(MapVersion(this.m_SqlVersion), DbView.Commands.cmd.Fields);
		//    String commandString = String.Format(commandTemplate, tableName, "");
		//    DataSet DSet = new DataSet();
		//    DataTable schemaTable = new Helper(this.GetAccessor()).GenericQuery(GetConnectionString(), commandString, tableName);
		//    schemaTable.TableName = tableName;
		//    SqlTableSchema schema = new SqlTableSchema();
		//    schema.Populate(schemaTable, "ColumnName", "Type", "Length", "PKey?", "Auto?",
		//                                 "FKey?", "Index?", "Calc?", "Def?", "Default", "Nulls?");

		//    return schema;
		//}

		// wrong. This is search source code
		protected String GetSourceCode_Impl(DbView.ViewContext context, String objectName)
		{
			throw new NotImplementedException();
		}
		
		// Do the special suff in here
		protected DbView.Command ExecuteDriverAction_Impl(DbView.Command command, object window, DbViewDriver.IConnectionConfig connections, String searchText, out bool refreshGrid)
		{
			DbView.Command next;
			refreshGrid = this.extensionCommandProcessor.ProcessCommand(command, window, connections, searchText, out next);
			return next;
		}

		protected void GetTableSchema_Impl(String tableName, out DataTable table, bool full)
		{
			String template = SqlCeTemplates.Templates.FetchTemplate(this.MapVersion(this.sqlVersion), DbView.Commands.Cmnd.Fields);
			String command = String.Format(template, tableName, "");
			table = new Helper(this.GetAccessor()).GenericQuery(this.connectionString, command, tableName);
		}

		// build the custom main menu 
		protected DbViewDriver.CustomMenuItem BuildCustomMenu_Impl()
		{
			if (this.sqlVersion == Version.UNKNOWN) this.DetermineSqlProperties();
			return new SqlServerPopupBuilder(this).BuildCustomMenu();
		}

		// build a pop-up menu 
		protected DbViewDriver.CustomMenuItem BuildPopupMenu_Impl(DbView.Command command)
		{
			return new SqlServerPopupBuilder(this).BuildPopupMenu(command);
		}

		///////////////////////////////////////////////////////////////////////
		// Implementation
		String TableNameFromContext(DbView.ViewContext context)
		{
			// if there is a non-null view definition the table is 
			// there.
			if (context.ViewDef != null)
				return context.ViewDef.TableName;
			// otherwise this is (hopefully) a popup from the table list
			else
				return context.ObjectName;

			// return this.m_LastTableName;
		}

		String SourceCommand(DbView.ViewContext context)
		{
			String objectName = this.ObjectNameFromContext(context);
			return objectName; // see GetSource below. Only requires the name
		}
		
		String ObjectNameFromContext(DbView.ViewContext context)
		{
			// works for views, tables, Procs.
			return context.ObjectName;
		}

		// Configure driver
		protected bool ConfigureConnection_Impl(DbViewDriver.IConnectionConfig connectionConfig)
		{
			// display the dialog.
			using (SqlCeConnectionEditForm dlg = new SqlCeConnectionEditForm(connectionConfig, this))
			{ return (dlg.ShowDialog() == System.Windows.Forms.DialogResult.OK) ? true : false; }
		}

		///////////////////////////////////////////////////////////////
		// table script parsing
		DataTable ParseCreateScript(String tableScript)
		{
			DataTable dt = DataTableHelpers.MakeDataTable("FIELDS", "Field Name|Type|Length", "SSI");
			// simple approach. Break on commas filter out rubbish
			String[] lines = tableScript.Split(',');
			lines[0] = this.TidyFirstLine(lines[0]); // remove create table
			int lastField = this.FindLastField(lines); // lines may include constraints
			lines[lastField] = this.TidyLastLine(lines[lastField]);
			for (int idx = 0; idx <= lastField; ++idx)
			{
				String[] parts = lines[idx].Trim().Split(' ');
				dt.Rows.Add(new object[] { parts[0], parts[1], null });
			}
			return dt;
		}
		String TidyFirstLine(String line1) // remove create table
		{
			int pos = line1.IndexOf('(');
			if (pos == -1) return line1;
			++pos;
			return line1.Substring(pos, line1.Length - pos);
		}

		String TidyLastLine(String lastLine) // remove )
		{
			int pos = lastLine.IndexOf(')');
			if (pos == -1) return lastLine;
			--pos;
			return lastLine.Substring(1, pos);
		}

		int FindLastField(String[] lines)
		{
			return lines.GetLength(0) -1; // 1 -> 0 based
		}


		/////////////////////////////////////////////////////////////////
		// private
		// objects that break up the functionality of the driver
		TableCommandProcessor tableCommandProcessor;
		SourcecodeCommandProcessor sourcecodeCommandProcessor;
		ExtensionCommandProcessor extensionCommandProcessor;
		String connectionString;
		//String m_LastTableName; // hacky. there is a real problem with this item
		protected enum Version {UNKNOWN=0, SqlCE35, SqlCE40}; // 
		Version sqlVersion = Version.SqlCE35;   //only one version for now.
		bool isCaseSensitive = false;
	}
}
